/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package goodsBean;

import DBConnection.ATeamObject;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import javax.ejb.*;
import javax.jms.*;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * Bean for goods from database.
 * @author XPYCT
 */
public class GoodsBean implements EntityBean {
    private EntityContext entityContext;
    private DataSource dataSource;
    private java.sql.Connection connection;
    private Integer goodsId;
    private Integer categoryId;
    private Integer manufacturerId;
    private Integer price;
    private String nameOfGoods;
    private String nameOfCategory;
    private String nameOfManufacture;
    private String goodsDescription;
    private Integer number;
    private String goodsCreateDate;
    private String goodsLastModifyDate;
    private Boolean isUpdate = false;
    private MessageProducer publisher;
    private ConnectionFactory connectionFactory;
    private javax.jms.Connection factoryConnection;
    private Queue messageQueue;
    private Session messageSession;
       
    public GoodsBean() {
        
        System.out.println("In GoodsBean");
    }
    
    public void ejbActivate() {
        
        System.out.println("In ejbPassivate");
    }

    /**
     * Create new goods
     * @param ato Parameters of new goods
     * @return goodsId
     * @throws CreateException
     * @see CreateException
     */
    public Integer ejbCreate(ATeamObject ato)
            throws CreateException {
        
        System.out.println("In ejbCreate");
        
        String getNextSeq = "SELECT ObjectSeq.nextval FROM dual";
        
        String addInObjectQuery = 
                "INSERT INTO Object"
                + " VALUES(?,?,?,4,?)";
    
        String addInParamsQuery = 
                "INSERT INTO Params VALUES(?,?,?)";
    
        String addInReferenceQuery =
                "INSERT INTO Reference VALUES(?,?,9)";
        
        String executeProcedure =
                "BEGIN"+
                " GETNEXTSEQ;"+
                " END;";
        
        String getDateQuery = 
                "SELECT VALUE,"
                + " ATTRID FROM PARAMS"
                + " WHERE OBJECTID = ?"
                + " AND ATTRID IN (21,22)"
                + " ORDER BY ATTRID";
        
        String getNameQuery = 
                "SELECT Object.Name"
                + " FROM Object"
                + " WHERE ObjectId = ?"
                + " AND ObjectTypeId = ?";
        
        String searchMan =
                "SELECT * FROM OBJECT"
                + " WHERE OBJECTID = ?"
                + " AND OBJECTTYPEID = 2";
        
        String setDateQuery = 
                "INSERT INTO Params"
                + " VALUES(?,?,TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'))";
        
        
        PreparedStatement createStatement = null;
        ResultSet resultSet = null;
        MapMessage message = null;
        String messageDescription;
        
        categoryId = ato.getParentId();
        price = ato.getPrice();
        nameOfGoods = ato.getName();
        goodsDescription = ato.getObjectDescription();
        number = ato.getNumber();
        
        try {
            
            setConnection();
            
            createStatement = 
                    connection.prepareStatement(executeProcedure);
            createStatement.execute();
            
            if (ato.getObjectId() == 0) {
                
                createStatement = connection.prepareStatement(getNextSeq);
                resultSet = createStatement.executeQuery();
                if (!resultSet.next()){
                    
                    createStatement.close();
                    throw new CreateException();
                }
                goodsId = resultSet.getInt(1);
                ato.setObjectId(resultSet.getInt(1));
            } else {
                
                goodsId = ato.getObjectId();
            }
            
            createStatement = connection.prepareStatement(addInObjectQuery);
            createStatement.setInt(1, ato.getObjectId());
            createStatement.setInt(2, ato.getParentId());
            createStatement.setString(3, ato.getName());
            createStatement.setString(4, ato.getObjectDescription()); 
            if (createStatement.executeUpdate() != 1) {
                
                createStatement.close();
                throw new CreateException(
                        "Impossible to create goods object");
            }
            
            createStatement = connection.prepareStatement(setDateQuery);
            createStatement.setInt(1, goodsId);
            createStatement.setInt(2, 21);
            if(createStatement.executeUpdate() != 1){
                
                createStatement.close();
                throw new NoSuchEntityException(
                        "The goods create date hasn't set");
            }

            createStatement = connection.prepareStatement(setDateQuery);
            createStatement.setInt(1, goodsId);
            createStatement.setInt(2, 22);
            if(createStatement.executeUpdate() != 1){
                
                createStatement.close();
                throw new NoSuchEntityException(
                        "The goods modify date hasn't set");
            }
        
            createStatement = connection.prepareStatement(getNameQuery);
            createStatement.setInt(1, ato.getParentId());
            createStatement.setInt(2, 1);
            resultSet = createStatement.executeQuery(); 
            if (!resultSet.next()){
                
                createStatement.close();
                throw new CreateException();
            }
            nameOfCategory = resultSet.getString(1);
            
            createStatement = connection.prepareStatement (
                    addInParamsQuery);
            createStatement.setInt(1, ato.getObjectId());
            createStatement.setInt(2, 10);
            createStatement.setString(
                    3, new Integer(ato.getPrice()).toString());         
            if (createStatement.executeUpdate() != 1) {
                
                createStatement.close();
                throw new CreateException("Impossible to set goods price");
            }
            
            createStatement.setInt(1, ato.getObjectId());
            createStatement.setInt(2, 11);
            createStatement.setString(3, new Integer(
                    ato.getNumber()).toString());         
            if (createStatement.executeUpdate() != 1) {
                
                createStatement.close();
                throw new CreateException("Impossible to set goods numder");
            }
            
            createStatement = connection.prepareStatement(getDateQuery);
            createStatement.setInt(1, ato.getObjectId());
            resultSet = createStatement.executeQuery(); 
            if (!resultSet.next()){
                
                createStatement.close();
                throw new CreateException();
            }
            
            goodsCreateDate = resultSet.getString("Value");
            resultSet.next();
            goodsLastModifyDate = resultSet.getString("Value");
            
            createStatement = connection.prepareStatement(searchMan);
            createStatement.setInt(1, ato.getManufacturerId());
            resultSet = createStatement.executeQuery();
            
            if (resultSet.next()) {
                
                manufacturerId = ato.getManufacturerId();
                
                createStatement = connection.prepareStatement (
                        addInReferenceQuery);
                createStatement.setInt(1, ato.getManufacturerId());                
                createStatement.setInt(2, ato.getObjectId());                
                if (createStatement.executeUpdate() != 1) {
                    
                    createStatement.close();
                    throw new CreateException(
                            "Impossible to set goods reference to manufacturer");
                }
                
                createStatement = connection.prepareStatement(getNameQuery);
                createStatement.setInt(1, ato.getManufacturerId());
                createStatement.setInt(2, 2);
                resultSet = createStatement.executeQuery(); 
                if (!resultSet.next()) {
                    
                    createStatement.close();
                    throw new CreateException();
                }
                nameOfManufacture = resultSet.getString(1);
            } else {
                
                manufacturerId = 0;
                nameOfManufacture = "Не известен";
            }
            
            createStatement.close();
            
            
            setPublisher();
            
            messageDescription = "Id = " + goodsId + ", ";
            messageDescription += "name = " + nameOfGoods + ", ";
            messageDescription += "parentId = " + categoryId + ", ";
            messageDescription += "manufacturerId = " + manufacturerId + ", ";
            messageDescription += "price = " + price + ", ";
            messageDescription += "number = " + number + ".";
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", goodsId);
            message.setInt("ActionId", 1);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 4, 0);
           
            messageDescription = "Child was created with id = " + goodsId + ".";
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", categoryId);
            message.setInt("ActionId", 3);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 2, 0);
            
            return ato.getObjectId();
        }
        
        catch (Exception ex) {
            throw new EJBException("ejbCreate:" + ex.getMessage());
        } finally {
            
            closeConnection();
            unsetPublisher();
        }
    }
    
    /**
     * Find goods by id of category.
     * @return Collection of goodsId
     */
    public Collection ejbFindAllByCategoryId(ATeamObject ato) {
        
        System.out.println("In ejbFindAllByCategoryId");
        
        PreparedStatement findAllByCategoryIdStatement = null;
        ResultSet resultSet = null;
        ArrayList goodsIds = new ArrayList();
        
        String getAllGoodOfCategoryQuery =
                "SELECT ObjectId" +
                " FROM Object" +
                " WHERE OBJECTTYPEID = 4" +
                " AND ParentId = ?" +
                " ORDER BY ObjectId";
        
        try {
            
            setConnection();
            
            findAllByCategoryIdStatement = connection.prepareStatement (
                    getAllGoodOfCategoryQuery);
            findAllByCategoryIdStatement.setInt(1, ato.getParentId());
            resultSet = findAllByCategoryIdStatement.executeQuery();            
            if (!resultSet.next()) {
                
                findAllByCategoryIdStatement.close();
                return goodsIds;
            }
            
            Integer goodId;
            do {
                
                goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
        
            findAllByCategoryIdStatement.close();
            
            return goodsIds;
        } catch (SQLException ex) {
            
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find goods by id of manufacturer.
     * @return Collection of goodsId
     */
    public Collection ejbFindAllByManufacturerId(ATeamObject ato) {
        
        System.out.println("In ejbFindAllByManufacturerId");
        
        PreparedStatement findAllByManufacturerIdStatement = null;
        ResultSet resultSet = null;
        ArrayList goodsIds = new ArrayList();
        
        String AllGoodOfManufactureQuery =
                "SELECT Reference.ObjectId" +
                " FROM Reference" +
                " WHERE Reference.ReferenceId = ?" +
                " ORDER BY ObjectId";
                
        try {
            
            setConnection();
            
            findAllByManufacturerIdStatement = connection.prepareStatement (
                    AllGoodOfManufactureQuery);
            findAllByManufacturerIdStatement.setInt(1, ato.getManufacturerId());
            resultSet = 
                    findAllByManufacturerIdStatement.executeQuery();            
            if (!resultSet.next()) {
                
                findAllByManufacturerIdStatement.close();
                return goodsIds;
            }
            
            Integer goodId;
            do {
                
                goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
        
            findAllByManufacturerIdStatement.close();
            
            return goodsIds;
        } catch (SQLException ex) {
            
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find all goods in database.
     * @return Collection of goodsId
     */
    public Collection ejbFindAllGoods() {
        
        System.out.println("In ejbFindAllGoods");        
        
        PreparedStatement findAllGoodsStatement = null;
        ResultSet resultSet = null;
        ArrayList goodsIds = new ArrayList();
        
        String allGoodsQuery =
                "SELECT OBJECT.OBJECTID" +
                " FROM OBJECT" +
                " WHERE OBJECT.OBJECTTYPEID = 4" +
                " ORDER BY OBJECT.OBJECTID";
        
        try {
            
            setConnection();
            
            findAllGoodsStatement = connection.prepareStatement(
                    allGoodsQuery);
            resultSet = findAllGoodsStatement.executeQuery();            
            if (!resultSet.next()) {
                
                findAllGoodsStatement.close();
                return goodsIds;
            }
            
            Integer goodId;
            do {
                
                goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
            
            findAllGoodsStatement.close();
            return goodsIds;
        } catch (SQLException ex) {
            
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find goods by all criteria.
     * @return Collection of goodsId
     */
    public Collection ejbFindByAllCriteria(ATeamObject ato) {
        
        System.out.println("In ejbFindByAllCriteria");
        
        boolean itFirstCriteria =true;
        boolean findByName=false;
        boolean findByPrice = false;
        boolean findByCategory = false;
        boolean findByManufacture = false;
        boolean findByPriceBetween = false;
        
        PreparedStatement GoodsByAllCriteriaQueryStat = null;
        ResultSet resultSet = null;
        ArrayList goodsIds = new ArrayList();
        
        String findGoodsByAllCriteriaQuery = 
                "SELECT DISTINCT Params.ObjectID"+
                " FROM Params"+
                " WHERE ";
              
        if (ato.getName()!=null) {
            
            findGoodsByAllCriteriaQuery += 
                    " Params.ObjectId IN"
                    + " (SELECT ObjectId" +
                    " FROM Object"+
                    " WHERE Name LIKE ?"
                    + " AND ObjectTypeId = 4)";
            
            itFirstCriteria = false;
            findByName = true;
        }
        
        if (ato.getPrice()!=0) {
            
            if(!itFirstCriteria) {
                findGoodsByAllCriteriaQuery += " AND";
            }
            else {
                
                itFirstCriteria = false;
            }

            findGoodsByAllCriteriaQuery +=
                    " Params.ObjectId IN"
                    + " (SELECT Params.ObjectId" +
                    " FROM Params"+
                    " WHERE Params.AttrId = 10"+
                    " AND CAST(Params.Value AS INTEGER)";
            
            switch (ato.getTypeOfPrice()) {
                
                case 1:
                    findGoodsByAllCriteriaQuery += " =?)";
                    break;
                
                case 2:
                    findGoodsByAllCriteriaQuery += " <?)";
                    break;
                
                case 3:
                    findGoodsByAllCriteriaQuery += " >?)";
                    break;
                
                case 4:
                    findGoodsByAllCriteriaQuery +=
                            " >? AND CAST(Params.Value AS INTEGER) < ?)";
                    findByPriceBetween=true;
                    break;
            }
            findByPrice = true;
        }
        if(ato.getParentId() != 0) {
            
            if(!itFirstCriteria) {
                
                findGoodsByAllCriteriaQuery += " AND";
            }
            else {
                
                itFirstCriteria = false;
            }
            
            findGoodsByAllCriteriaQuery +=
                    " Params.ObjectId IN"
                    + " (SELECT ObjectId" +
                    " FROM Object"+
                    " WHERE ParentId = ?"
                    + " AND ObjectTypeId = 4)";
            findByCategory = true;
        }
        if(ato.getManufacturerId() != 0) {
            
            if(!itFirstCriteria) {
                
                findGoodsByAllCriteriaQuery += " AND";
            }
            else {
                
                itFirstCriteria = false;
            }
            
            findGoodsByAllCriteriaQuery +=
                    " Params.ObjectId IN"
                    + " (SELECT ObjectId" +
                    " FROM Reference"+
                    " WHERE ReferenceId = ?"
                    + " AND AttrId = 9)";
            findByManufacture = true;
        }
        findGoodsByAllCriteriaQuery += " ORDER BY OBJECTID";
        
        try {
            
            setConnection();
            
            GoodsByAllCriteriaQueryStat =
                connection.prepareStatement(findGoodsByAllCriteriaQuery);
            int parameterIndex=1;

            if(findByName) {
                
                if(!ato.getContainName()) {
                    GoodsByAllCriteriaQueryStat.setString(
                            parameterIndex++, ato.getName());
                }
                else {
                    GoodsByAllCriteriaQueryStat.setString(
                            parameterIndex++, "%" + ato.getName() + "%");
                }
            }

            if(findByPrice) {
                
                GoodsByAllCriteriaQueryStat.setInt(
                        parameterIndex++, ato.getPrice());
                if (findByPriceBetween) {
                    
                    GoodsByAllCriteriaQueryStat.setInt(
                            parameterIndex++, ato.getPriceMax());
                }
            }

            if(findByCategory) {
                
                GoodsByAllCriteriaQueryStat.setInt(
                        parameterIndex++, ato.getParentId());
            }

            if(findByManufacture) {
                
                GoodsByAllCriteriaQueryStat.setInt(
                        parameterIndex++, ato.getManufacturerId());
            }
            resultSet = GoodsByAllCriteriaQueryStat.executeQuery();
            
            if (!resultSet.next()) {
                
                    GoodsByAllCriteriaQueryStat.close();
                    return goodsIds;
                }
            
            Integer goodId;
            do {
                
                goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
            
            GoodsByAllCriteriaQueryStat.close();
            
            return goodsIds;
        } catch (Exception ex) {
            
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find goods by name.
     * @return Collection of goodsId
     */
    public Collection ejbFindByNameEquals(ATeamObject ato) {
        
        System.out.println("In ejbFindByNameEquals"); 
        
        PreparedStatement findByNameEqualsStatement = null;
        ResultSet resultSet = null;
        ArrayList goodsIds = new ArrayList();
        
        String GoodsByNameEqualsQuery =
            "SELECT ObjectId" +
            " FROM Object" +
            " WHERE Name LIKE ?" +
            " ORDER BY ObjectId";
        
        try {
            
            setConnection();
            
            findByNameEqualsStatement = connection.prepareStatement(
                    GoodsByNameEqualsQuery);
            findByNameEqualsStatement.setString(1, ato.getName());
            resultSet = findByNameEqualsStatement.executeQuery();            
            if (!resultSet.next()) {
                
                findByNameEqualsStatement.close();
                return goodsIds;
            }
            
            Integer goodId; 
            do {
                
                goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());
            
            findByNameEqualsStatement.close();
           
            return goodsIds;
        } catch (SQLException ex) {
            
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
    }

    /**
     * Find goods by price.
     * @return Collection of goodsId
     */
    public Collection ejbFindByPriceEquals(ATeamObject ato) {
        
        System.out.println("In ejbFindByPriceEquals");
        
        PreparedStatement findByPriceEqualsStatement = null;
        ResultSet resultSet = null;
        ArrayList goodsIds = new ArrayList();
        
        String GoodsByPriceEqualsQuery =
                "SELECT ObjectId" +
                " FROM Object" +
                " WHERE ObjectId IN "
                + "(SELECT ObjectId" +
                " FROM Params" +
                " WHERE AttrId = 8" + 
                " AND Value = ?)" +
                " ORDER BY ObjectId";
        
        try {
            
            setConnection();
            
            findByPriceEqualsStatement = connection.prepareStatement(
                    GoodsByPriceEqualsQuery);
            findByPriceEqualsStatement.setInt(1, ato.getPrice());
            resultSet = findByPriceEqualsStatement.executeQuery();
            if (!resultSet.next()) {
                
                findByPriceEqualsStatement.close();
                return goodsIds;
            }
            
            Integer goodId;
            do {
                
                goodId = new Integer(resultSet.getInt("ObjectId"));
                goodsIds.add(goodId);
            } while (resultSet.next());   
            
            findByPriceEqualsStatement.close();

            return goodsIds;
        } catch (SQLException ex) {
            
            throw new EJBException(ex);
        } finally {
            
            closeConnection();
        }
    }
    
    /**
     * Find goods by id.
     * @return goodsId
     * @throws ObjectNotFoundException 
     * @see ObjectNotFoundException
     */
    public Integer ejbFindByPrimaryKey (Integer primaryKey) 
            throws ObjectNotFoundException {
        
        System.out.println("In ejbFindByPrimaryKey"); 
        
        PreparedStatement findByPrimaryKeyStatement = null;
        ResultSet rs = null;
        String findByPrimaryKeyQuery = 
                "SELECT ObjectId"
                + " FROM Object" +
                " WHERE ObjectId = ?" + 
                " AND ObjectTYPEID = 4";
        
        try {
            
            setConnection();
            
            findByPrimaryKeyStatement = connection.prepareStatement(
                    findByPrimaryKeyQuery);
            findByPrimaryKeyStatement.setInt(1, primaryKey);
            rs = findByPrimaryKeyStatement.executeQuery();            
            if (!rs.next()) {
                
                throw new ObjectNotFoundException("The goods aren't found");
            }
            else {
                
                findByPrimaryKeyStatement.close();
                return primaryKey;
            }
        } catch (SQLException ex) {
            
            throw new ObjectNotFoundException("ejbFindByPrimaryKey:"
                    + ex.getMessage());
        } finally {
            
            closeConnection();
        }
    }
    
    public void ejbLoad() {
        
        System.out.println("In ejbLoad");
        
        PreparedStatement loadStatement = null;
        ResultSet resultSet = null;
                
        String getParamsOfGoodsQuery =
                "SELECT Params.AttrId, Params.Value "
                + "FROM Params, OBJECT "
                + "WHERE Params.ObjectId = ?"
                + " AND OBJECT.OBJECTID = ?"
                + " AND OBJECT.OBJECTTYPEID = 4"
                + " AND NOT PARAMS.AttrId IN (21,22)"
                + " ORDER BY AttrId";
        
        String getDateQuery = 
                "SELECT TO_DATE(VALUE, 'DD.MM.YYYY HH24:MI:SS') AS VALUE,"
                + " ATTRID FROM PARAMS"
                + " WHERE OBJECTID = ?"
                + " AND ATTRID IN (21,22)"
                + " ORDER BY ATTRID";
        
        String getGoodsDescriptionQuery =
                "SELECT OBJECT.OBJECTDESCRIPTION" +
                " FROM OBJECT" +
                " WHERE OBJECT.OBJECTID = ?";
        
        String getIdCategoryOfGoodQuery =
                "SELECT OBJECT.PARENTID FROM Object" +
                " WHERE Object.ObjectId = ?" +
                " AND Object.ObjectTYPEID = 4";
        
        String getIdManufacturerOfGoodQuery =
                "SELECT Reference.ReferenceId" +
                " FROM Reference, OBJECT" +
                " WHERE Reference.ObjectId = ?"
                + " AND Reference.AttrId = 9" +
                " AND OBJECT.OBJECTID = ?" +
                " AND OBJECT.OBJECTTYPEID = 4";
        
        String getNameQuery = 
                "SELECT Object.Name"
                + " FROM Object"
                + " WHERE ObjectId = ?"
                + " AND ObjectTypeId = ?";
            
        try {
            
            setConnection();
            
            goodsId = (Integer)entityContext.getPrimaryKey();
            
            loadStatement = connection.prepareStatement(
                    getParamsOfGoodsQuery);
            loadStatement.setInt(1, goodsId);
            loadStatement.setInt(2, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()) {
                
                loadStatement.close();
                throw new NoSuchEntityException("The goods aren't found");
            }
            price = resultSet.getInt("Value");
            resultSet.next();
            number = resultSet.getInt("Value");
            
            loadStatement = connection.prepareStatement(getDateQuery);
            loadStatement.setInt(1, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()) {
                
                loadStatement.close();
                throw new NoSuchEntityException("The goods date isn't found");
            }
            goodsCreateDate = resultSet.getString("Value");
            resultSet.next();
            goodsLastModifyDate = resultSet.getString("Value");
            
            loadStatement = connection.prepareStatement(
                    getGoodsDescriptionQuery);
            loadStatement.setInt(1, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()) {
                
                loadStatement.close();
                throw new NoSuchEntityException(
                        "The goods description isn't found");
            }
            goodsDescription = resultSet.getString("OBJECTDESCRIPTION");
            
            loadStatement = connection.prepareStatement(
                    getIdCategoryOfGoodQuery);
            loadStatement.setInt(1, goodsId);
            resultSet = loadStatement.executeQuery();
            if (!resultSet.next()) {
                
                loadStatement.close();
                throw new NoSuchEntityException(
                        "The goods category isn't found");
            }
            categoryId = resultSet.getInt("PARENTID");
            
            loadStatement = connection.prepareStatement(
                    getIdManufacturerOfGoodQuery);
            loadStatement.setInt(1, goodsId);
            loadStatement.setInt(2, goodsId);
            resultSet = loadStatement.executeQuery(); 
            if (resultSet.next()) {
                
                manufacturerId = resultSet.getInt("ReferenceId");
                resultSet = null;
                
                loadStatement = connection.prepareStatement(getNameQuery);
                loadStatement.setInt(1, manufacturerId);
                loadStatement.setInt(2, 2);
                resultSet = loadStatement.executeQuery(); 
                if (!resultSet.next()){
                    
                    loadStatement.close();
                    throw new NoSuchEntityException(
                            "The goods manufacturer name isn't found");
                }
                nameOfManufacture = resultSet.getString(1);
                
            } else {
                manufacturerId = 0;
                nameOfManufacture = "Не известен";
            }
            
            loadStatement = connection.prepareStatement(getNameQuery);
            loadStatement.setInt(1, goodsId);
            loadStatement.setInt(2, 4);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()) {
                
                loadStatement.close();
                throw new NoSuchEntityException(
                        "The goods name isn't found");
            }
            nameOfGoods = resultSet.getString(1);
            
            loadStatement.setInt(1, categoryId);
            loadStatement.setInt(2, 1);
            resultSet = loadStatement.executeQuery(); 
            if (!resultSet.next()) {
                
                loadStatement.close();
                throw new NoSuchEntityException(
                        "The goods category name isn't found");
            }
            nameOfCategory = resultSet.getString(1);
            
            loadStatement.close();
            
        } catch (SQLException ex) {
            
            throw new EJBException(ex.getMessage());
        } finally {
            
            closeConnection();
        }
    }

    public void ejbPassivate() {
        
        System.out.println("In ejbPassivate");
    }
    
    public void ejbPostCreate(ATeamObject ato)
            throws CreateException {
        
        System.out.println("In ejbPostCreate");
    }
    
    public void ejbRemove() throws RemoveException {
        
        System.out.println("In ejbRemove");
        
        PreparedStatement removeStatement = null;
        MapMessage message = null;
        String delGoodByIdQuery = 
                "DELETE FROM Object" +
                " WHERE Object.ObjectId = ?"
                + " AND OBJECT.OBJECTTYPEID = 4";
    
        try {
            
            setConnection();
            
            removeStatement = connection.prepareStatement (
                    delGoodByIdQuery);
            removeStatement.setInt(1, goodsId);
            removeStatement.execute();
            
            removeStatement.close();
            
            setPublisher();
            
            String messageDescription = "Child was deleted with id = " +
                    goodsId + ".";
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", categoryId);
            message.setInt("ActionId", 4);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 1, 0);
        } catch (Exception ex) {
        
            ex.printStackTrace(System.err);
            throw new EJBException("ejbRemove:" + ex.getMessage());
        } finally {
            
            closeConnection();
            unsetPublisher();
        }
    }
    
    public void ejbStore() {
        
        PreparedStatement storeStatement = null;
        ResultSet resultSet = null;
        
        String addInReferenceQuery =
                "INSERT INTO Reference VALUES(?,?,9)";
        
        String checkManufacturer =
                "SELECT ReferenceId"
                + " FROM REFERENCE"
                + " WHERE ObjectId = ?"
                + " AND AttrId = 9";
        
        String editCategoryOfGoodInObjectQuery =
                "UPDATE Object"+ 
                " SET Object.ParentId = ?"+
                " WHERE Object.ObjectId = ?"
                + " AND OBJECT.OBJECTTYPEID = 4";
        
        String editGoodsDescriptionQuery =
               "UPDATE OBJECT" +
               " SET OBJECT.OBJECTDESCRIPTION = ?" +
               " WHERE OBJECT.OBJECTID = ?" +
               " AND OBJECT.OBJECTTYPEID = 4";
        
        String editManufactureOfGoodInReferenceQuery = 
                "UPDATE Reference"+
                " SET Reference.ReferenceId = ?"+
                " WHERE Reference.ObjectId = ?";

        String editNameOfGoodInObjectQuery = 
                "UPDATE Object"+ 
                " SET Object.Name = ?"+
                " WHERE Object.ObjectId = ?"
                + " AND OBJECT.OBJECTTYPEID = 4";

        String editNumberOfGoodQuery = 
                "UPDATE Params"+ 
                " SET Params.Value = ?"+
                " WHERE Params.ObjectId = ?"+
                " AND Params.AttrId = 11";

        String editPriceOfGoodQuery = 
                "UPDATE Params"+ 
                " SET Params.Value = ?"+
                " WHERE Params.ObjectId = ?"+
                " AND Params.AttrId = 10";
        
        if (isUpdate) {
            System.out.println("In ejbStore");
                
            try {
                
                setConnection();

                storeStatement = connection.prepareStatement (
                        editCategoryOfGoodInObjectQuery);
                storeStatement.setInt(1, categoryId);
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1) {
                    
                    storeStatement.close();
                    throw new NoSuchEntityException(
                            "The goods category isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                if (manufacturerId != 0) {
                    
                    storeStatement = connection.prepareStatement(
                            checkManufacturer);
                    storeStatement.setInt(1, goodsId);
                    resultSet = storeStatement.executeQuery();
                    
                    if(resultSet.next()) {
                        
                        storeStatement = connection.prepareStatement (
                                editManufactureOfGoodInReferenceQuery);
                        storeStatement.setInt(1, manufacturerId);
                        storeStatement.setInt(2, goodsId);
                        if(storeStatement.executeUpdate() != 1) {
                            
                            storeStatement.close();
                            throw new NoSuchEntityException(
                                    "The manufacturer isn't found");
                        }
                    
                    } else {
                        
                        storeStatement = connection.prepareStatement(
                                addInReferenceQuery);
                        storeStatement.setInt(1, manufacturerId);
                        storeStatement.setInt(2, goodsId);
                        if (storeStatement.executeUpdate() != 1) {
                            
                            storeStatement.close();
                            throw new NoSuchEntityException(
                            "Impossible to set goods reference to manufacturer");
                        }
                    }
                }

                storeStatement = connection.prepareStatement (
                        editNameOfGoodInObjectQuery);
                storeStatement.setString(1, nameOfGoods);                
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1) {
                    
                    storeStatement.close();
                    throw new NoSuchEntityException("The goods aren't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement (
                        editPriceOfGoodQuery);
                storeStatement.setString(1, price.toString());
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1) {
                    
                    storeStatement.close();
                    throw new NoSuchEntityException(
                            "The goods price isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement (
                        editNumberOfGoodQuery);
                storeStatement.setString(1, number.toString());
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1) {
                    
                    storeStatement.close();
                    throw new NoSuchEntityException(
                            "The goods number isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement = connection.prepareStatement(
                        editGoodsDescriptionQuery);
                storeStatement.setString(1, goodsDescription);
                storeStatement.setInt(2, goodsId);
                if(storeStatement.executeUpdate() != 1) {
                    
                    storeStatement.close();
                    throw new NoSuchEntityException(
                            "The goods dscription isn't found");
                }
                updateDate(storeStatement, connection, goodsId);

                storeStatement.close();
            } catch (SQLException ex) {
                
                throw new EJBException("ejbStore:" + ex.getMessage());
            } finally {
                
                closeConnection();
                isUpdate = false;
            }
        }
    }
    
    /**
     * Closes connection with database
     */
    private void closeConnection() {
        try {
            
            if (connection != null) {
                
                connection.close();
                System.out.println("Connection closed");
            }
        } catch (SQLException ex) {
            
            throw new EJBException("closeConnection:" + ex.getMessage());
        } finally {
            
            connection = null;
        }
    }
    
    /**
     * returns value of <i>goodsCreateDate</i>
     * @return goodsCreateDate
     */
    public String getGoodsCreateDate() {
        
        return goodsCreateDate;
    }
    
    /**
     * returns value of <i>goodsDescription</i>
     * @return goodsDescription
     */    
    public String getGoodsDescription() {
        
        return goodsDescription;
    }
    
    /**
     * returns value of <i>goodsId</i>
     * @return goodsId
     */
    public int getGoodsId() {
        
        return goodsId;
    }
    
    /**
     * returns value of <i>goodsLastModifyDate</i>
     * @return goodsLastModifyDate
     */
    public String getGoodsLastModifyDate() {
        
        return goodsLastModifyDate;
    }
    
    /**
     * returns value of <i>categoryId</i>
     * @return categoryId
     */
    public int getCategoryId() {
        
        return categoryId;
    }
    
    /**
     * returns value of <i>manufacturerId</i>
     * @return manufacturerId
     */
    public int getManufactureId() {
        
        return manufacturerId;
    }
    
    /**
     * returns value of <i>price</i>
     * @return price
     */
    public int getPrice() {
        
        return price;
    }
    
    /**
     * returns value of <i>nameOfGoods</i>
     * @return nameOfGoods
     */
    public String getNameOfGoods() {
        
        return nameOfGoods;
    }
    
    /**
     * returns value of <i>nameOfCategory</i>
     * @return nameOfCategory
     */
    public String getNameOfCategory() {
        
        return nameOfCategory;
    }
    
    /**
     * returns value of <i>nameOfManufacture</i>
     * @return nameOfManufacture
     */
    public String getNameOfManufacture() {
        
        return nameOfManufacture;
    }
    
    /**
     * returns value of <i>number</i>
     * @return number
     */
    public int getNumber() {
        
        return number;
    }
    
    private void sendEditMessage(String messageDescription) {
        
        MapMessage message = null;
        try {
            
            setPublisher();
            
            message = messageSession.createMapMessage();
            message.setInt("ObjectId", goodsId);
            message.setInt("ActionId", 2);
            message.setString("Description", messageDescription);
            
            publisher.send(message, DeliveryMode.NON_PERSISTENT, 3, 0);
        } catch (JMSException ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex.getMessage());
        } finally {
            
            unsetPublisher();
        }
    }
        
    /**
     * Sets value of designated parameter in <i>categoryId</i>
     * @param categoryId The value to be set in <i>categoryId</i>
     */
    public void setCategoryId(Integer categoryId) {
        
        String messageDescription = "ParentId is changed from " +
                    this.categoryId + " to " + categoryId + ".";
            
        this.categoryId = categoryId;
        isUpdate = true;
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * Open connection with database
     */
    private void setConnection() {
        
        if (connection == null) {
            
            try {
                
                connection = dataSource.getConnection();
                System.out.println("Connection is ready");
            } catch (SQLException ex){
                
                throw new EJBException(ex);
            }
        }
    }
    
    public void setEntityContext(EntityContext entityContext) {
        
        System.out.println("In setEntityContext");
        
        InitialContext initialContext = null;
        
        this.entityContext = entityContext;
        try {
            
            initialContext = new InitialContext();
            dataSource = (DataSource) initialContext.lookup(
                    "java:/ATeamConnectionPool");
            System.out.println("DataSource is found");
        } catch (NamingException ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException("setEntityContext:" + ex.getMessage());
        }
        setConnection();    
    }

    /**
     * Sets value of designated parameter in <i>goodsDescription</i>
     * @param goodsDescription The value to be set in <i>goodsDescription</i>
     */
    public void setGoodsDescription(String goodsDescription) {
        
        String messageDescription = "Description is changed.";
        
        this.goodsDescription = goodsDescription;
        isUpdate = true;
        
        sendEditMessage(messageDescription);
    }

    
    /**
     * Sets value of designated parameter in <i>manufactureId</i>
     * @param manufactureId The value to be set in <i>manufactureId</i>
     */
    public void setManufactureId(Integer manufacturerId) {
        
        String messageDescription = "ManufacturerId is changed from " +
                    this.manufacturerId + " to " + manufacturerId + ".";
        
        this.manufacturerId = manufacturerId;
        isUpdate = true;
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * Sets value of designated parameter in <i>price</i>
     * @param price The value to be set in <i>price</i>
     */
    public void setPrice(Integer price) {
        
        String messageDescription = "Price is changed from " +
                    this.price + " to " + price + ".";
        
        this.price = price;
        isUpdate = true;
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * open new Publisher
     */
    private void setPublisher() {
        
        InitialContext initialContext = null;
        
        try {
            
            initialContext = new InitialContext();
            connectionFactory = (ConnectionFactory)initialContext.lookup(
                    "/XConnectionFactory");
            messageQueue = (Queue)initialContext.lookup(
                    "/queue/GoodsMessageQueue");
            factoryConnection = connectionFactory.createConnection();
            messageSession = factoryConnection.createSession(
                        false, Session.AUTO_ACKNOWLEDGE);
            publisher = messageSession.createProducer(messageQueue);
        } catch (Exception ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex);
        }
    }
    
    /**
     * Sets value of designated parameter in <i>nameOfGoods</i>
     * @param nameOfGoods The value to be set in <i>nameOfGoods</i>
     */
    public void setNameOfGoods(String nameOfGoods) {
        
        String messageDescription = "Name is changed from " +
                    this.nameOfGoods + " to " + nameOfGoods + ".";
        
        this.nameOfGoods = nameOfGoods;
        isUpdate = true;
        
        sendEditMessage(messageDescription);
    }
    
    /**
     * Sets value of designated parameter in <i>number</i>
     * @param number The value to be set in <i>number</i>
     */
    public void setNumber(Integer number) {
        
        String messageDescription = "Number is changed from " +
                    this.number + " to " + number + ".";
        
        this.number = number;
        isUpdate = true;
        
        sendEditMessage(messageDescription);
    }
    
    public void unsetEntityContext() {
        System.out.println("In unsetEntityContext");
        
        entityContext = null;
        if(connection != null) {
            try {
                
                connection.close();
            }
            catch(SQLException sqlException) {
                
                throw new EJBException(sqlException);
            }
            finally {
                
                connection = null;
            }
        }     
    }
    
    /**
     * close Publisher
     */
    private void unsetPublisher() {
        
        try {
            
            publisher.close();
            messageSession.close();
            factoryConnection.close();        
        } catch (JMSException ex) {
            
            ex.printStackTrace(System.err);
            throw new EJBException(ex);
        } finally {
            
            publisher = null;
            messageSession = null;
            factoryConnection = null;
            messageQueue = null;
            connectionFactory = null;
        }
    }
    
    private void updateDate(
            PreparedStatement statement, java.sql.Connection connection,
            Integer goodsId) throws SQLException {
        
        String updateDateQuery = 
                "UPDATE Params"
                + " SET VALUE = TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS')"
                + " WHERE ObjectId = ?"
                + " AND ATTRID = 22";
        
        statement = connection.prepareStatement(updateDateQuery);
        statement.setInt(1, goodsId);
        if(statement.executeUpdate() != 1){
            throw new NoSuchEntityException(
                    "The goods modify date isn't found");
        }
    }
}